-- EXEC SP_CALCULATE_CUMMULATIVE_COST_BYPRODUCT
ALTER PROC SP_CALCULATE_CUMMULATIVE_COST_BYPRODUCT 
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @LOT_ID			BIGINT
	DECLARE @PROCESSSTEP_ID 	BIGINT
	DECLARE @PS_TYPE		VARCHAR(50) 
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR		INT
	DECLARE @PATH_PROCESSSTEP_ID INT
	DECLARE @STD_COST NUMERIC(10,4)
	DECLARE @DB_STD_COST NUMERIC(10,4)
	DECLARE @YIELD FLOAT
	DECLARE @SORT_STD_COST NUMERIC(10,4)
	DECLARE @SORT_YIELD FLOAT
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @GDW INT
	DECLARE @TOTAL_STD_COST NUMERIC(10,4)
	DECLARE @NDW INT
	DECLARE @CUMM_STD_COST NUMERIC(10,4)
	DECLARE @PS_TYPE_NAME VARCHAR(20)
	DECLARE @BOM_CURSOR CURSOR
	DECLARE @P_TYPE VARCHAR(20)
	DECLARE @ASSM_PROCESSSTEP_ID INT
	DECLARE @MEM_PROCESSSTEP_ID  INT
	DECLARE @MEM_DIE_QTY		 INT
	DECLARE @MEM_DIE_COST		 NUMERIC(10,4)	
	DECLARE @MEM_COST			 NUMERIC(10,4)
	DECLARE @YIELDED_COST		NUMERIC(10,4)	
	DECLARE @DB_YIELDED_COST	NUMERIC(10,4)
	DECLARE @DIES_PER_PACKAGE	INT
	DECLARE @IS_TRANSIT	INT
	
	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE


	CREATE TABLE #PROCESSSTEPS_CUMM_COST_TABLE ( LOT_ID INT, PROCESSSTEP_ID INT, CUMM_STD_COST NUMERIC(10,4), YIELDED_STD_COST NUMERIC(10,4), PS_TYPE_NAME VARCHAR(50), IS_TRANSIT INT )

	CREATE TABLE #PATH_TABLE (ROW_ID INT, PATH_NUMBER INT ,PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), STD_COST NUMERIC(10,4), YIELD FLOAT, YIELDED_STD_COST NUMERIC(10,4))

	-- THIS IS TO TAKE OF DIE BANK PARTS THAT ARE USED IN MULTIPLE PRODUCTS
	INSERT INTO #PROCESSSTEPS_CUMM_COST_TABLE (LOT_ID, PROCESSSTEP_ID, PS_TYPE_NAME)
		SELECT -1, BOM_ProcessStep_ID, 'Die Bank' FROM assembly_bom_setup_table ABST, Product P, Process_Step PS WHERE ABST.Bom_ProcessStep_Id = P.ProcessStep_ID AND
		P.ProcessStep_ID = PS.ProcessStep_ID and P.P_Type IS NULL AND PS.PS_TYPE = 'Die Bank'
	
	INSERT INTO #PROCESSSTEPS_CUMM_COST_TABLE (LOT_ID, PROCESSSTEP_ID, PS_TYPE_NAME, IS_TRANSIT )
		SELECT DISTINCT LT.LOT_ID, LT.PROCESSSTEP_ID, PS_TYPE_NAME, 1 FROM LOT_TABLE LT, PROCESS_STEP PS
		WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_CURRENT_STATUS != 'Close' AND LT.ProcessStep_Id = 145
		UNION
		SELECT DISTINCT LT.LOT_ID, LT.PROCESSSTEP_ID, PS_TYPE_NAME, 0 FROM SHIPPED_LOT SL,LOT_TABLE LT, PROCESS_STEP PS
		WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS != 'CLOSE' AND LT.ProcessStep_Id = 145
	
	DECLARE @PROCESS_STEPS_CURSOR CURSOR
	DECLARE @PATH_CURSOR CURSOR 
	SET @PROCESS_STEPS_CURSOR = CURSOR FOR 
							SELECT DISTINCT PROCESSSTEP_ID FROM #PROCESSSTEPS_CUMM_COST_TABLE ORDER BY PROCESSSTEP_ID DESC
	
	OPEN @PROCESS_STEPS_CURSOR
	FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #PATH_TABLE(ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD)
		SELECT ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, 
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) AS STD_COST,
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
				WHEN @QUARTER_NO = 2 THEN Q2_YIELD
				WHEN @QUARTER_NO = 3 THEN Q3_YIELD
				ELSE Q4_YIELD
			 END) AS YIELD
			 FROM 
			(SELECT Distinct ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE 
				FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSSTEP_ID,0) ) AS XYZ 
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT ON XYZ.PROCESSSTEP_ID = UVCT.PROCESSSTEP_ID AND YEAR(fin_year) = @YEAR
			 ORDER BY PATH_NUMBER, ROW_ID DESC
		
		SET @PATH_CURSOR = CURSOR FOR 
							SELECT PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD FROM #PATH_TABLE WHERE PATH_NUMBER = 1 ORDER BY ROW_ID DESC
		OPEN @PATH_CURSOR
		FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @STD_COST = MAX(ISNULL(STD_COST,0)) FROM #PATH_TABLE WHERE PS_TYPE = @PS_TYPE
			SELECT @YIELD = MAX(ISNULL(YIELD,0)) FROM #PATH_TABLE WHERE PS_TYPE = @PS_TYPE AND ISNULL(STD_COST,0) = ISNULL(@STD_COST,0)
			IF ( @PS_TYPE = 'DIE BANK' )
			BEGIN
				SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM #PATH_TABLE WHERE PS_TYPE = 'FAB'
				SELECT @GDW = PARAMETER_VALUE FROM Process_Parameter WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
				IF ( @GDW > 0 )
				BEGIN
					SET @YIELDED_COST = ISNULL(@YIELDED_COST,0)/@GDW
				END
			END

			IF @PS_TYPE = 'Assembly'
			BEGIN
				SELECT @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM #PATH_TABLE WHERE PS_TYPE = @PS_TYPE
				SET @BOM_CURSOR = CURSOR FOR SELECT ISNULL(Qty,1), bom_processStep_ID, p_type FROM assembly_bom_setup_table abst, product p WHERE abst.bom_processstep_id = p.processstep_id and Assembly_ProcessStep_Id = @ASSM_PROCESSSTEP_ID
				OPEN @BOM_CURSOR
				FETCH NEXT FROM @BOM_CURSOR INTO @MEM_DIE_QTY, @MEM_PROCESSSTEP_ID,@P_TYPE
				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF @P_TYPE = 'BOM'
					BEGIN
						SELECT @MEM_DIE_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
										WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
										WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
										ELSE Q4_STANDARD_COST
									 END)  FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @MEM_PROCESSSTEP_ID
					END
					ELSE
					BEGIN
						SELECT @MEM_DIE_COST = CUMM_STD_COST FROM #PROCESSSTEPS_CUMM_COST_TABLE WHERE PROCESSSTEP_ID = @MEM_PROCESSSTEP_ID
					END
					SET @MEM_COST = ISNULL(@MEM_COST, 0.0 ) + ISNULL(@MEM_DIE_COST,0) * @MEM_DIE_QTY
					FETCH NEXT FROM @BOM_CURSOR INTO @MEM_DIE_QTY, @MEM_PROCESSSTEP_ID, @P_TYPE
				END
				CLOSE @BOM_CURSOR
				DEALLOCATE @BOM_CURSOR
					--SELECT @MEM_DIE_QTY = ISNULL(Qty,0), @MEM_PROCESSSTEP_ID = bom_processStep_ID FROM assembly_bom_setup_table WHERE Assembly_ProcessStep_Id = @ASSM_PROCESSSTEP_ID
					--SET @MEM_COST = ISNULL(@MEM_DIE_COST,0) * @MEM_DIE_QTY
			END
			
			/*IF ( @PATH_PROCESSSTEP_ID = @PROCESSSTEP_ID ) --NOT CONSIDERING YEIELD AS THE CURRENT PROCESS IS NOT COMPLETED
			BEGIN
				SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0) + ISNULL(@MEM_COST,0)
			END
			ELSE*/
			BEGIN
				IF ( @YIELD IS NULL OR @YIELD = 0 )
				BEGIN
					SET @YIELD = 100.00
				END

				IF @PS_TYPE = 'Assembly'
				BEGIN
					SET @TOTAL_STD_COST = @YIELDED_COST + ISNULL(@MEM_COST,0)
					SET @YIELDED_COST = ((ISNULL(@YIELDED_COST,0)+ ISNULL(@MEM_COST,0) )/( @YIELD/100.00)) +  ISNULL(@STD_COST,0)
				END
				ELSE
				BEGIN
					SET @TOTAL_STD_COST = @YIELDED_COST
					SET @YIELDED_COST = (ISNULL(@YIELDED_COST,0)+ ISNULL(@MEM_COST,0) + (ISNULL(@STD_COST,0)) )/( @YIELD/100.00 )
				END
			END

			SET @SORT_STD_COST = 0
			SET @SORT_YIELD = 0
			SET @FAB_PROCESSSTEP_ID = NULL
			SET @GDW = 0
			SET @NDW = 0
			SET @MEM_COST = 0
			FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		END
		CLOSE @PATH_CURSOR
		DEALLOCATE @PATH_CURSOR
		
		--SET @YIELDED_COST = 0
		--SELECT @YIELDED_COST = ISNULL(YIELDED_STD_COST,0) FROM #PATH_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		
		DELETE FROM #PATH_TABLE
		
		UPDATE #PROCESSSTEPS_CUMM_COST_TABLE SET CUMM_STD_COST = @TOTAL_STD_COST, YIELDED_STD_COST = @YIELDED_COST WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		
		SET @TOTAL_STD_COST = 0
		FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESSSTEP_ID
	END
	CLOSE @PROCESS_STEPS_CURSOR
	DEALLOCATE @PROCESS_STEPS_CURSOR
	DECLARE @LOT_CURSOR CURSOR
	SELECT * FROM #PROCESSSTEPS_CUMM_COST_TABLE
	SET @LOT_CURSOR = CURSOR FOR SELECT LOT_ID, CUMM_STD_COST, YIELDED_STD_COST, PS_TYPE_NAME, IS_TRANSIT FROM #PROCESSSTEPS_CUMM_COST_TABLE
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @CUMM_STD_COST, @YIELDED_COST, @PS_TYPE_NAME, @IS_TRANSIT
	WHILE @@FETCH_STATUS = 0
	BEGIN

		IF (@IS_TRANSIT = 0 ) -- IF LOT IS NOT IN TRANSIT THEN THE CURRENT PROCESS COST IS NOT CONSIDERED
		 BEGIN
			SET @CUMM_STD_COST = @YIELDED_COST
		 END

		IF ( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT')
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = @CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( WAFER_QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = @CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID
		END
		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @CUMM_STD_COST, @YIELDED_COST, @PS_TYPE_NAME, @IS_TRANSIT
	END
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR
	
END 